BI Workbooks/Query

BI workbooks in DataGOL

BI workbooks (also referred to as SQL Workbooks) are virtual workbooks that allow users to write custom SQL queries directly within the Visualizer. They provide greater control over data transformation, filtering, and parameterization, making them especially useful for advanced and dynamic analytical use cases.

BI workbooks are typically built on top of existing regular workbooks and are ideal when the default data model does not fully meet specific reporting or filtering needs.

When to use a BI workbook?

BI workbooks are especially useful when:

• You are working with a flattened data model and need more control over filters, joins, or transformations.
• You want to create dynamic views using parameters that regular workbooks cannot support.
• You need to define custom calculated fields using SQL expressions.
• You want to extend a golden layer without modifying the original model.

Whether you're an analyst or an advanced business user, BI workbooks offer the flexibility to shape data the way your scenario demands — all while staying within the familiar DataGOL interface.

Key benefits of BI workbooks

• Custom SQL Logic: Write custom queries for highly specific views.
• Parameterized Analysis: Use parameters to dynamically filter or modify the dataset.
• Layered Exploration: Extend a flattened base dataset with your own logic and calculated fields.

Creating a BI workbook

1. Open the Visualizer and select the base workbook you want to build upon.
2. Click on New Query.

3. Choose the appropriate Data Provider from the top-right corner.

   Caution

   This selection is permanent and cannot be changed later.

4. Start writing your SQL query. The editor includes auto-complete suggestions for available workbooks, columns, formulas, and parameters within the workspace.

5. Click Run to execute your query. If successful, the resulting columns (Dimensions, Metrics, Dates) will be available for use.

6. Use these columns to start building widgets like charts, tables, or KPIs.

7. When you pin your first widget to a dashboard, DataGOL will prompt you to name and save the BI Workbook.

SQL editor autocomplete

The SQL editor in DataGOL offers intelligent autocomplete functionality to help speed up query building and reduce errors. Suggestions are based on the tables, columns, formulas, and parameters available within your selected data connection. This is how it works:

• As you begin typing your SQL query (e.g., SELECT * FROM), the editor automatically suggests relevant tables available in the current connection.

• Once a table is selected, the system autocompletes the query by assigning its internal reference name.

  

  Example:

  -- User input:
  SELECT * FROM pms_data-pms_data.csv
  
  -- Autocompleted as:
  SELECT * FROM app_connection_read.table_1663904
  

  This maps the selected table to its internal reference and completes the syntax for immediate execution.

  TIP

  You can also use autocomplete to reference columns, saved formulas, and parameters, making query building faster and more reliable.

How to edit an existing BI workbook?

Do the following to edit an existing BI workbook:

1. Open the Visualizer and select the desired BI Workbook from the source list.

2. Click on Edit Query.

   

3. Modify the existing SQL query as needed.

4. Click Run to execute the updated query. Once the query runs successfully, a new list of columns (dimensions, metrics, dates) will be displayed.

5. You can now:

   • Update these changes to the existing BI workbook,
   • Create a new BI workbook with the newly modified query.

Tip

Always validate that your existing widgets columns reflect the updated structure of the workbook and no data is lost.

How to identify a BI workbook?

Once created, BI workbooks are accessible and reusable like any other workbook:

1. Click Select Source in the Visualizer.

• Your newly created BI Workbook will appear in the list alongside regular workbooks.
• BI workbooks are clearly labeled with a distinct BI Workbook tag to differentiate them.
• Upon selecting a BI Workbook, you can view the underlying SQL query directly in the editor.

2. Click Select to begin creating widgets with the chosen BI Workbook.

Limitations

• Currently supported datatypes on BI workbooks include single line text, date, boolean, and number. This means conversion from single line text to single select or numeric to currency is limited
• The datatypes from workbook do not flow to the BI workbook (if in workbook there is a single select column on BI workbook it would appear as single line text only)